﻿using System;
using System.Data;
using System.Text.RegularExpressions;

namespace NPoco
{
    public class PagingHelper
    {
        public static Regex RxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        public static Regex RxOrderBy = new Regex(@"\bORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\.\[\] ""`])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\.\[\] ""`])+(?:\s+(?:ASC|DESC))?)*(?!.*FROM)", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

        public struct SqlParts
        {
            public string Sql;
            public string SqlCount;
            public string SqlSelectRemoved;
            public string SqlOrderBy;
            public string SqlUnordered;
            public string SqlColumns;
        }

        public static bool SplitSql(string sql, out SqlParts parts)
        {
            parts.Sql = sql;
            parts.SqlSelectRemoved = null;
            parts.SqlCount = null;
            parts.SqlOrderBy = null;
            parts.SqlUnordered = sql.Trim().Trim(';');
            parts.SqlColumns = "*";

            // Extract the columns from "SELECT <whatever> FROM"
            var m = RxColumns.Match(sql);
            if (!m.Success) return false;

            // Save column list  [and replace with COUNT(*)]
            Group g = m.Groups[1];
            parts.SqlSelectRemoved = sql.Substring(g.Index);

            // Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
            m = RxOrderBy.Match(parts.Sql);
            if (m.Success)
            {
                g = m.Groups[0];
                parts.SqlOrderBy = g.ToString();
                parts.SqlUnordered = RxOrderBy.Replace(parts.SqlUnordered, "");
            }

            parts.SqlCount = string.Format(@"SELECT COUNT(*) FROM ({0}) peta_tbl", parts.SqlUnordered);

            return true;
        }
    }
}
